<?php
session_start();
include_once("config/config.inc.php");
include_once("monthly.class.php");

$lsql = "select settings_value from settings where id = '3'";
$lres = mysql_query($lsql);
list($lobbyid)=mysql_fetch_row($lres);


function getFnbReport($month,$year)
{
	$num = cal_days_in_month(CAL_GREGORIAN, $month, $year) ;

	$sql = "select settings_value from settings where id = '1'";
	$res = mysql_query($sql);
	list($value) = mysql_fetch_row($res);

	$ret = "<div style='font-weight:bold'>".strtoupper($value)."<br>
	FOOD AND BEVERAGES SALES SUMMARY<br>
	FOR THE MONTH OF ".strtoupper(getMonthName($month))." ".$year."</div><br><br>";
	$ret .= "<table border=1 cellpadding=3 cellspacing=0>";
	$ret .= "<tr>";	
	$ret .= "<th>Category</th>";	
	$ret .= "<th>Total</th>";
	$ret .= "</tr>";

	$start = date('Y-m-d H:i:s', strtotime('-1800 seconds',strtotime($year."-".$month."-1 00:00:00")));
	$end = date('Y-m-d H:i:s', strtotime('+1800 seconds',strtotime($year."-".$month."-$num 23:59:59")));

	$sql = "select  `datetime` from `shift-transactions` where `datetime` between '$start' and '$end' 
		and shift ='start' order by `datetime` asc limit 0,1";
	$res = mysql_query($sql);
	list($startdt)=mysql_fetch_row($res);
		
	$sql = "select  `datetime` from `shift-transactions` where `datetime` between '$start' and '$end' 
		and shift ='end' order by `datetime` desc limit 0,1";
	$res = mysql_query($sql);
	list($enddt)=mysql_fetch_row($res);
	
	

	$sql = "select count(fnbsales_id),a.category_id from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id";
			
	$sql .= " group by a.category_id order by count(fnbsales_id) desc limit 0,10";
	$res = mysql_query($sql) or die(mysql_error().$sql);

	while(list($cnt,$catid)=mysql_fetch_row($res))
	{
		$_sql = "select food_category_name from food_categories where food_category_id = '$catid'";
		$_res = mysql_query($_sql);
		list($catname)=mysql_fetch_row($_res);
		$ret .= "<tr>";
		$ret .= "<td>".$catname."</td>";
		$ret .= "<td>".$cnt."</td>";		
		$ret .= "</tr>";	
	}
	$ret .= "</tr>";
	$ret .= "</table>";

	$ret .= "<br><br>";
	

	/*$sql = "select count(fnbsales_id),a.item_id from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id";
			
	$sql .= " group by a.item_id order by count(fnbsales_id) desc limit 0,10";
	$res = mysql_query($sql) or die(mysql_error().$sql);*/

	$sql = "select count(fnbsales_id),a.category_id from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id";
			
	$sql .= " group by a.category_id order by count(fnbsales_id) desc limit 0,10";
	$res = mysql_query($sql) or die(mysql_error().$sql);

	$ret .= "<table border=0 cellpadding=3 cellspacing=0>";
	$ret .= "<tr>";	
	$ret .= "<th>Category</th>";	
	$ret .= "<th>Item Name</th>";	
	$ret .= "<th>Total</th>";
	$ret .= "</tr>";

	while(list($cnt,$catid)=mysql_fetch_row($res))
	{
		$_sql = "select food_category_name from food_categories where food_category_id = '$catid'";
		$_res = mysql_query($_sql);
		list($catname)=mysql_fetch_row($_res);


		$__sql = "select count(fnbsales_id),a.item_id from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id
			and a.category_id = '$catid'";
		$__sql .= " group by a.item_id order by count(fnbsales_id) desc limit 0,10";
		$__res = mysql_query($__sql) or die(mysql_error().$__sql);
		while(list($cnt2,$fnbid)=mysql_fetch_row($__res))
		{		
			$___sql = "select fnb_name from fnb where  fnb_id = '$fnbid'";
			$___res = mysql_query($___sql);
			list($fnbname) = mysql_fetch_row($___res);
			$ret .= "<tr>";
			$ret .= "<td>".$catname."</td>";
			$ret .= "<td>".$fnbname."</td>";
			$ret .= "<td>".$cnt2."</td>";		
			$ret .= "</tr>";
			$catname = "&nbsp;";
		}
		
	}
	$ret .= "</tr>";
	$ret .= "</table>";
	return $ret;
}

function getMonthDropdown($name="month", $selected=null)
	{
			$dd = '<select name="'.$name.'" id="'.$name.'">';

			$months = array(
					1 => 'January',
					2 => 'February',
					3 => 'March',
					4 => 'April',
					5 => 'May',
					6 => 'June',
					7 => 'July',
					8 => 'August',
					9 => 'September',
					10 => 'October',
					11 => 'November',
					12 => 'December');
			/*** the current month ***/
			$selected = is_null($selected) ? date('n', time()) : $selected;

			for ($i = 1; $i <= 12; $i++)
			{
					$dd .= '<option value="'.$i.'"';
					if ($i == $selected)
					{
							$dd .= ' selected';
					}
					/*** get the month ***/
					$dd .= '>'.$months[$i].'</option>';
			}
			$dd .= '</select>';
			return $dd;
	}

	function getYearDropdown($name="year", $selected=null)
	{
			$dd = '<select name="'.$name.'" id="'.$name.'">';

			$months = array(
					1 => '2009',
					2 => '2010',
					3 => '2011',
					4 => '2012',
					5 => '2013',
					6 => '2014',
					7 => '2015',
					8 => '2016');
		   
			$selected = is_null($selected) ? date('n', time()) : $selected;

			for ($i = 1; $i <= 8; $i++)
			{
					$dd .= '<option value="'.$months[$i].'"';
					if ($months[$i] == $selected)
					{
							$dd .= ' selected';
					}
					
					$dd .= '>'.$months[$i].'</option>';
			}
			$dd .= '</select>';
			return $dd;
	}

	function getMonthName($i)
	{
		$months = array(
					1 => 'January',
					2 => 'February',
					3 => 'March',
					4 => 'April',
					5 => 'May',
					6 => 'June',
					7 => 'July',
					8 => 'August',
					9 => 'September',
					10 => 'October',
					11 => 'November',
					12 => 'December');
		return $months[$i];
	}
?>
<style>
		.printable {
			font-family: sans-serif;
			font-size: 14px;
			font-weight: 550;
			}
		.report{
			font-family: sans-serif;
			font-size: 14px ;
			text-align:left;
			font-weight: 550;
				
		}
		.report td{
			padding-bottom:12px;
		}
		.summary{
			font-family: sans-serif;
			font-size: 14px;
			text-align:left;
			font-weight: 550;
		}
</style>
<script src="../js/jquery.js" type="text/javascript"></script>
<script type="text/javascript" src="../js/jquery.print.js"></script>
<script type="text/javascript">
 
		// When the document is ready, initialize the link so
		// that when it is clicked, the printable area of the
		// page will print.
		$(document).ready(function(){		
				$("a").attr( "href", "javascript:void( 0 )" ).click(
						function(){
							// Print the DIV.
							$(".printable2").print();
							$(".printable").print(); 							
							// Cancel click event.
							return( false );
						});
 
			
		});
 
</script>
<form name=myform method=post>
<div>
Month: <? echo getMonthDropdown("ddlmonth",$_POST["ddlmonth"]); ?>
<br>
<br>
Year: <? echo getYearDropdown("ddlyear",$_POST["ddlyear"]); ?>
</div>
<br>
<input type='submit' value='Search' name='btnSearch' />
<br>
<br>
<a href="#">Print Report</a>
<br>
<br>
<div class='printable'>
<? if($_POST){ echo getFnbReport($_POST["ddlmonth"],$_POST["ddlyear"]);} ?>
</div><br />
<a href="#">Print Report</a>
</form>